package com.socool.sinofaith.util;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AndroidSQLiteOpenHelper extends SQLiteOpenHelper {
	// 数据库名称
	public static final String DBNAME = "Sinofaith.db";
	// 数据库版本
	public static final int VERSION = 3;

	public AndroidSQLiteOpenHelper(Context context) {
		super(context, DBNAME, null, VERSION);
	}

	// 创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// 任务信息
		String sifTaskDB = "create table if not exists Sif_Task (Task_ID text ,TaskName text,TaskDesc text,IsPush text,StartTM text,"
				+ "EndTM text,HasAddress text,IsPublic text,IsPreview text,AnswerTimes text,PhotoCompression text,GoBackSet text,Mobile text,CreateTime text,ModifyTime text, primary key (Task_ID,Mobile))";
		db.execSQL(sifTaskDB);

		// 任务地址
		String sifTaskAddressDB = "create table if not exists Sif_TaskAddress (TaskAddress_ID text primary key,Task_ID text,Province text,City text,Address text,"
				+ "GPS text,ExecNum text,ExecRange text,CreateTime text,ModifyTime text)";
		db.execSQL(sifTaskAddressDB);

		// 二级任务
		String sifSubTaskDB = "create table if not exists Sif_SubTask (SubTask_ID text primary key,Task_ID text,Template_ID text,TemplateName text,HasChild text,CreateTime text,ModifyTime text)";
		db.execSQL(sifSubTaskDB);

		// 三级任务（车架号类）

		String sifSubTemplateDB = "create table if not exists Sif_SubTemplate (Stemp_ID text primary key,SubTask_ID text,TempNO text ,Task_ID text, IsCare text,CreateTime text,ModifyTime text,IsMark,Detail text," +
				"LastDate text,Brand text,Model text,Color text, FinanceDate text)";
		db.execSQL(sifSubTemplateDB);

		// 问题
		String sifTaskQuestionDB = "create table if not exists Sif_TaskQuestion (TaskQuestion_ID text,Template_ID text,Question text,QuestionType text,HasOtherOption text,"
				+ "OrderIndex text,IsRequired text,PhotoNum text,PhotoMode text,PhotoDesc text,Task_ID text,ZaikuFlag text,JumpTo text,CreateTime text,ModifyTime text, primary key (Task_ID,TaskQuestion_ID))";
		db.execSQL(sifTaskQuestionDB);

		// 问题选项
		String sifQuestionOptionsDB = "create table if not exists Sif_QuestionOptions (OP_ID text,TaskQuestion_ID text,OptionValue text,IsDefault text,JumpTo text,Task_ID text,RemindFlag text,CreateTime text,ModifyTime text, primary key (OP_ID,Task_ID))";
		db.execSQL(sifQuestionOptionsDB);

		// 答题
		String questionAnswerDB = "create table if not exists QuestionAnswer (ID INTEGER primary key AUTOINCREMENT,Task_ID text,SubTask_ID text,Stemp_ID text,Submiter text,TaskQuestion_ID text,Answer text,FileCount text,OtherAnswer text,AnswerType text,IsFinish text,Formto text,GPS text,GPSTime text,RemindFlag text,CreateTime text,ModifyTime text)";
		db.execSQL(questionAnswerDB);
	}

	// 更新表
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		this.deleteDB(db);
		this.onCreate(db);
	}

	// 删除表
	private void deleteDB(SQLiteDatabase db) {
		// 任务信息
		db.execSQL("drop table if exists Sif_Task");

		// 任务地址
		db.execSQL("drop table if exists Sif_TaskAddress");

		// 二级任务
		db.execSQL("drop table if exists Sif_SubTask");

		// 三级任务（车架号类）
		db.execSQL("drop table if exists Sif_SubTemplate");

		// 问题
		db.execSQL("drop table if exists Sif_TaskQuestion");

		// 问题选项
		db.execSQL("drop table if exists Sif_QuestionOptions");

		// 答题
		db.execSQL("drop table if exists QuestionAnswer");
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		//
		
		super.onOpen(db);
		if (!checkColumnExist(db, "Sif_SubTemplate", "IsMark")){
			db.execSQL("alter table Sif_SubTemplate add IsMark text");
		}
			
		if (!checkColumnExist(db, "Sif_SubTemplate", "Detail")){
			db.execSQL("alter table Sif_SubTemplate add Detail text");
		}

		if (!checkColumnExist(db, "Sif_SubTemplate", "LastDate")){
			db.execSQL("alter table Sif_SubTemplate add LastDate text");
		}

		if (!checkColumnExist(db, "Sif_SubTemplate", "Brand")){
			db.execSQL("alter table Sif_SubTemplate add Brand text");
		}
		
		if (!checkColumnExist(db, "Sif_SubTemplate", "Model")){
			db.execSQL("alter table Sif_SubTemplate add Model text");
		}
		
		if (!checkColumnExist(db, "Sif_SubTemplate", "FinanceDate")){
			db.execSQL("alter table Sif_SubTemplate add FinanceDate text");
		}
		
		if (!checkColumnExist(db, "Sif_SubTemplate", "Color")){
			db.execSQL("alter table Sif_SubTemplate add Color text");
		}

		
		



		
		
	}

	/**
	 * 方法1：检查某表列是否存在
	 * 
	 * @param db
	 * @param tableName
	 *            表名
	 * @param columnName
	 *            列名
	 * @return
	 */
	private boolean checkColumnExist(SQLiteDatabase db, String tableName,
			String columnName) {
		boolean result = false;
		Cursor cursor = null;
		try {
			// 查询一行
			cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0",
					null);
			result = cursor != null && cursor.getColumnIndex(columnName) != -1;
		} catch (Exception e) {
			Log.e("TAG", "checkColumnExists1..." + e.getMessage());
		} finally {
			if (null != cursor && !cursor.isClosed()) {
				cursor.close();
			}
		}
		return result;
	}

}
